/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     27-11-2012 14:39:34                          */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ITEM') and o.name = 'FK_TB_ITEM_FK_GRUPO__TB_GRUPO')
alter table TB_ITEM
   drop constraint FK_TB_ITEM_FK_GRUPO__TB_GRUPO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ORDEN_COMPRA') and o.name = 'FK_TB_ORDEN_FK_LOG_EN_TB_ORDEN')
alter table TB_ORDEN_COMPRA
   drop constraint FK_TB_ORDEN_FK_LOG_EN_TB_ORDEN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ORDEN_COMPRA') and o.name = 'FK_TB_ORDEN_FK_OC_ENC_TB_PERSO')
alter table TB_ORDEN_COMPRA
   drop constraint FK_TB_ORDEN_FK_OC_ENC_TB_PERSO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ORDEN_COMPRA') and o.name = 'FK_TB_ORDEN_FK_OC_EST_TB_ESTAD')
alter table TB_ORDEN_COMPRA
   drop constraint FK_TB_ORDEN_FK_OC_EST_TB_ESTAD
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ORDEN_COMPRA') and o.name = 'FK_TB_ORDEN_FK_ORDEN__TB_USUAR')
alter table TB_ORDEN_COMPRA
   drop constraint FK_TB_ORDEN_FK_ORDEN__TB_USUAR
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ORDEN_COMPRA_APROBADOR') and o.name = 'FK_TB_ORDEN_FK_OC_APR_TB_ORDEN')
alter table TB_ORDEN_COMPRA_APROBADOR
   drop constraint FK_TB_ORDEN_FK_OC_APR_TB_ORDEN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ORDEN_COMPRA_APROBADOR') and o.name = 'FK_TB_ORDEN_FK_OC_APR_TB_USUAR')
alter table TB_ORDEN_COMPRA_APROBADOR
   drop constraint FK_TB_ORDEN_FK_OC_APR_TB_USUAR
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ORDEN_COMPRA_DETALLE') and o.name = 'FK_TB_ORDEN_FK_OCDET__TB_ITEM')
alter table TB_ORDEN_COMPRA_DETALLE
   drop constraint FK_TB_ORDEN_FK_OCDET__TB_ITEM
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ORDEN_COMPRA_DETALLE') and o.name = 'FK_TB_ORDEN_FK_OCDET__TB_MONED')
alter table TB_ORDEN_COMPRA_DETALLE
   drop constraint FK_TB_ORDEN_FK_OCDET__TB_MONED
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ORDEN_COMPRA_DETALLE') and o.name = 'FK_TB_ORDEN_FK_OCDET__TB_UNIDA')
alter table TB_ORDEN_COMPRA_DETALLE
   drop constraint FK_TB_ORDEN_FK_OCDET__TB_UNIDA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ORDEN_COMPRA_DETALLE') and o.name = 'FK_TB_ORDEN_FK_OC_DET_TB_ORDEN')
alter table TB_ORDEN_COMPRA_DETALLE
   drop constraint FK_TB_ORDEN_FK_OC_DET_TB_ORDEN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ORDEN_COMPRA_LOG_APROBADOR') and o.name = 'FK_TB_ORDEN_FK_LOG_AP_TB_MOTIV')
alter table TB_ORDEN_COMPRA_LOG_APROBADOR
   drop constraint FK_TB_ORDEN_FK_LOG_AP_TB_MOTIV
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ORDEN_COMPRA_LOG_APROBADOR') and o.name = 'FK_TB_ORDEN_FK_LOG_AP_TB_USUAR')
alter table TB_ORDEN_COMPRA_LOG_APROBADOR
   drop constraint FK_TB_ORDEN_FK_LOG_AP_TB_USUAR
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ORDEN_COMPRA_LOG_APROBADOR') and o.name = 'FK_TB_ORDEN_FK_OCLOG__TB_ORDEN')
alter table TB_ORDEN_COMPRA_LOG_APROBADOR
   drop constraint FK_TB_ORDEN_FK_OCLOG__TB_ORDEN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ORDEN_COMPRA_LOG_ENVIO_MAIL') and o.name = 'FK_TB_ORDEN_FK_LOG_EN_TB_USUAR')
alter table TB_ORDEN_COMPRA_LOG_ENVIO_MAIL
   drop constraint FK_TB_ORDEN_FK_LOG_EN_TB_USUAR
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ORDEN_COMPRA_LOG_ESTADO') and o.name = 'FK_TB_ORDEN_FK_LOG_ES_TB_ESTAD')
alter table TB_ORDEN_COMPRA_LOG_ESTADO
   drop constraint FK_TB_ORDEN_FK_LOG_ES_TB_ESTAD
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ORDEN_COMPRA_LOG_ESTADO') and o.name = 'FK_TB_ORDEN_FK_LOG_ES_TB_ORDEN')
alter table TB_ORDEN_COMPRA_LOG_ESTADO
   drop constraint FK_TB_ORDEN_FK_LOG_ES_TB_ORDEN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ROL_USUARIO') and o.name = 'FK_TB_ROL_U_FK_ROL_RU_TB_ROL')
alter table TB_ROL_USUARIO
   drop constraint FK_TB_ROL_U_FK_ROL_RU_TB_ROL
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_ROL_USUARIO') and o.name = 'FK_TB_ROL_U_FK_ROL_US_TB_USUAR')
alter table TB_ROL_USUARIO
   drop constraint FK_TB_ROL_U_FK_ROL_US_TB_USUAR
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TB_USUARIO') and o.name = 'FK_TB_USUAR_FK_LOG_ES_TB_ORDEN')
alter table TB_USUARIO
   drop constraint FK_TB_USUAR_FK_LOG_ES_TB_ORDEN
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TB_ESTADOS_OC')
            and   type = 'U')
   drop table TB_ESTADOS_OC
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TB_GRUPO_ITEM')
            and   type = 'U')
   drop table TB_GRUPO_ITEM
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TB_ITEM')
            and   type = 'U')
   drop table TB_ITEM
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TB_MONEDA')
            and   type = 'U')
   drop table TB_MONEDA
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TB_MOTIVO')
            and   type = 'U')
   drop table TB_MOTIVO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TB_ORDEN_COMPRA')
            and   type = 'U')
   drop table TB_ORDEN_COMPRA
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TB_ORDEN_COMPRA_APROBADOR')
            and   type = 'U')
   drop table TB_ORDEN_COMPRA_APROBADOR
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TB_ORDEN_COMPRA_DETALLE')
            and   type = 'U')
   drop table TB_ORDEN_COMPRA_DETALLE
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TB_ORDEN_COMPRA_LOG_APROBADOR')
            and   type = 'U')
   drop table TB_ORDEN_COMPRA_LOG_APROBADOR
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TB_ORDEN_COMPRA_LOG_ENVIO_MAIL')
            and   type = 'U')
   drop table TB_ORDEN_COMPRA_LOG_ENVIO_MAIL
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TB_ORDEN_COMPRA_LOG_ESTADO')
            and   type = 'U')
   drop table TB_ORDEN_COMPRA_LOG_ESTADO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TB_PERSONA')
            and   type = 'U')
   drop table TB_PERSONA
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TB_ROL')
            and   type = 'U')
   drop table TB_ROL
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TB_ROL_USUARIO')
            and   type = 'U')
   drop table TB_ROL_USUARIO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TB_UNIDAD_MEDIDA')
            and   type = 'U')
   drop table TB_UNIDAD_MEDIDA
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TB_USUARIO')
            and   type = 'U')
   drop table TB_USUARIO
go

/*==============================================================*/
/* Table: TB_ESTADOS_OC                                         */
/*==============================================================*/
create table TB_ESTADOS_OC (
   DM_ESTADO            char(1)              not null,
   DG_ESTADO            varchar(50)          null,
   constraint PK_TB_ESTADOS_OC primary key (DM_ESTADO)
)
go

/*==============================================================*/
/* Table: TB_GRUPO_ITEM                                         */
/*==============================================================*/
create table TB_GRUPO_ITEM (
   DC_GRUPO_ITEM        varchar(20)          not null,
   DG_GRUPO_ITEM        varchar(80)          not null,
   constraint PK_TB_GRUPO_ITEM primary key (DC_GRUPO_ITEM)
)
go

/*==============================================================*/
/* Table: TB_ITEM                                               */
/*==============================================================*/
create table TB_ITEM (
   DC_GRUPO_ITEM        varchar(20)          not null,
   DC_ITEM              varchar(20)          not null,
   DG_ITEM              varchar(80)          null,
   constraint PK_TB_ITEM primary key (DC_GRUPO_ITEM, DC_ITEM)
)
go

/*==============================================================*/
/* Table: TB_MONEDA                                             */
/*==============================================================*/
create table TB_MONEDA (
   DC_MONEDA            smallint             not null,
   DG_MONEDA            varchar(80)          null,
   constraint PK_TB_MONEDA primary key (DC_MONEDA)
)
go

/*==============================================================*/
/* Table: TB_MOTIVO                                             */
/*==============================================================*/
create table TB_MOTIVO (
   DM_TIPO_MOTIVO       char(1)              not null,
   DC_TIPO_MOTIVO       smallint             not null,
   DG_MOTIVO            varchar(80)           null,
   constraint PK_TB_MOTIVO primary key (DM_TIPO_MOTIVO, DC_TIPO_MOTIVO)
)
go

/*==============================================================*/
/* Table: TB_ORDEN_COMPRA                                       */
/*==============================================================*/
create table TB_ORDEN_COMPRA (
   DN_OC                int                  not null,
   DF_ENVIO             datetime             null,
   DC_USUARIO           varchar(20)          null,
   DC_RUT_PROVEEDOR     varchar(20)          null,
   DF_COTIZACION        datetime             null,
   DF_SOLICITUD         datetime             null,
   DF_REGISTRO_OC       datetime             null,
   DC_REQUIRENTE        varchar(20)          null,
   DM_ESTADO            char(1)              null,
   constraint PK_TB_ORDEN_COMPRA primary key (DN_OC)
)
go

/*==============================================================*/
/* Table: TB_ORDEN_COMPRA_APROBADOR                             */
/*==============================================================*/
create table TB_ORDEN_COMPRA_APROBADOR (
   DN_OC                int                  not null,
   DC_USUARIO           varchar(20)          not null,
   DM_ESTADO            char(1)              not null,
   constraint PK_TB_ORDEN_COMPRA_APROBADOR primary key (DN_OC, DC_USUARIO, DM_ESTADO)
)
go

/*==============================================================*/
/* Table: TB_ORDEN_COMPRA_DETALLE                               */
/*==============================================================*/
create table TB_ORDEN_COMPRA_DETALLE (
   DN_OC                int                  not null,
   DN_SECUENCIA         smallint             not null,
   DC_GRUPO_ITEM        varchar(20)          null,
   DC_ITEM              varchar(20)          null,
   DC_MONEDA            smallint             null,
   DC_UNIDAD_MEDIDA     varchar(10)          null,
   DQ_CANTIDAD          float                null,
   DQ_PRECIO            money                null,
   constraint PK_TB_ORDEN_COMPRA_DETALLE primary key (DN_OC, DN_SECUENCIA)
)
go

/*==============================================================*/
/* Table: TB_ORDEN_COMPRA_LOG_APROBADOR                         */
/*==============================================================*/
create table TB_ORDEN_COMPRA_LOG_APROBADOR (
   DN_OC                int                  not null,
   DC_USUARIO           varchar(20)          not null,
   DF_REGISTRO          datetime             not null,
   DM_TIPO_MOTIVO       char(1)              not null,
   DC_TIPO_MOTIVO       smallint             not null,
   DG_OBSERVACION       varchar(255)         null,
   constraint PK_TB_ORDEN_COMPRA_LOG_APROBAD primary key (DN_OC, DC_USUARIO, DF_REGISTRO, DM_TIPO_MOTIVO, DC_TIPO_MOTIVO)
)
go

/*==============================================================*/
/* Table: TB_ORDEN_COMPRA_LOG_ENVIO_MAIL                        */
/*==============================================================*/
create table TB_ORDEN_COMPRA_LOG_ENVIO_MAIL (
   DN_OC                int                  not null,
   DF_ENVIO             datetime             not null,
   DC_USUARIO           varchar(20)          not null,
   DG_OBSERVACION       varchar(255)         null,
   constraint PK_TB_ORDEN_COMPRA_LOG_ENVIO_M primary key (DN_OC, DF_ENVIO, DC_USUARIO)
)
go

/*==============================================================*/
/* Table: TB_ORDEN_COMPRA_LOG_ESTADO                            */
/*==============================================================*/
create table TB_ORDEN_COMPRA_LOG_ESTADO (
   DN_OC                int                  not null,
   DF_REGISTRO          datetime             not null,
   DC_USUARIO           varchar(20)          not null,
   DM_ESTADO            char(1)              not null,
   constraint PK_TB_ORDEN_COMPRA_LOG_ESTADO primary key (DN_OC, DF_REGISTRO, DC_USUARIO, DM_ESTADO)
)
go

/*==============================================================*/
/* Table: TB_PERSONA                                            */
/*==============================================================*/
create table TB_PERSONA (
   DC_RUT               varchar(20)          not null,
   DG_RAZON_SOCIAL      varchar(80)          null,
   DG_GIRO_COMERCIAL    varchar(80)          null,
   DG_MAIL              varchar(80)          null,
   constraint PK_TB_PERSONA primary key (DC_RUT)
)
go

/*==============================================================*/
/* Table: TB_ROL                                                */
/*==============================================================*/
create table TB_ROL (
   DN_ROL               integer              not null,
   DG_ROL               varchar(80)          null,
   DQ_MONTO_MINIMO_APROBACION money                null,
   DQ_MONTO_MAXIMO_APROBACION money                null,
   DN_DIAS_APROBACION   smallint             null,
   constraint PK_TB_ROL primary key (DN_ROL)
)
go

/*==============================================================*/
/* Table: TB_ROL_USUARIO                                        */
/*==============================================================*/
create table TB_ROL_USUARIO (
   DN_ROL               integer              not null,
   DC_USUARIO           varchar(20)          not null,
   constraint PK_TB_ROL_USUARIO primary key (DN_ROL, DC_USUARIO)
)
go

/*==============================================================*/
/* Table: TB_UNIDAD_MEDIDA                                      */
/*==============================================================*/
create table TB_UNIDAD_MEDIDA (
   DC_UNIDAD_MEDIDA     varchar(10)          not null,
   DG_UNIDAD_MEDIDA     varchar(80)          null,
   constraint PK_TB_UNIDAD_MEDIDA primary key (DC_UNIDAD_MEDIDA)
)
go

/*==============================================================*/
/* Table: TB_USUARIO                                            */
/*==============================================================*/
create table TB_USUARIO (
   DC_USUARIO           varchar(20)          not null,
   DM_ESTADO            char(1)              null,
   DN_OC                int                  null,
   DF_REGISTRO          datetime             null,
   DG_USUARIO           varchar(50)          null,
   DG_PASSWORD          varchar(255)         null,
   DG_MAIL              varchar(80)          null,
   constraint PK_TB_USUARIO primary key (DC_USUARIO)
)
go

alter table TB_ITEM
   add constraint FK_TB_ITEM_FK_GRUPO__TB_GRUPO foreign key (DC_GRUPO_ITEM)
      references TB_GRUPO_ITEM (DC_GRUPO_ITEM)
go

alter table TB_ORDEN_COMPRA
   add constraint FK_TB_ORDEN_FK_LOG_EN_TB_ORDEN foreign key (DN_OC, DF_ENVIO, DC_USUARIO)
      references TB_ORDEN_COMPRA_LOG_ENVIO_MAIL (DN_OC, DF_ENVIO, DC_USUARIO)
go

alter table TB_ORDEN_COMPRA
   add constraint FK_TB_ORDEN_FK_OC_ENC_TB_PERSO foreign key (DC_RUT_PROVEEDOR)
      references TB_PERSONA (DC_RUT)
go

alter table TB_ORDEN_COMPRA
   add constraint FK_TB_ORDEN_FK_OC_EST_TB_ESTAD foreign key (DM_ESTADO)
      references TB_ESTADOS_OC (DM_ESTADO)
go

alter table TB_ORDEN_COMPRA
   add constraint FK_TB_ORDEN_FK_ORDEN__TB_USUAR foreign key (DC_REQUIRENTE)
      references TB_USUARIO (DC_USUARIO)
go

alter table TB_ORDEN_COMPRA_APROBADOR
   add constraint FK_TB_ORDEN_FK_OC_APR_TB_ORDEN foreign key (DN_OC)
      references TB_ORDEN_COMPRA (DN_OC)
go

alter table TB_ORDEN_COMPRA_APROBADOR
   add constraint FK_TB_ORDEN_FK_OC_APR_TB_USUAR foreign key (DC_USUARIO)
      references TB_USUARIO (DC_USUARIO)
go

alter table TB_ORDEN_COMPRA_DETALLE
   add constraint FK_TB_ORDEN_FK_OCDET__TB_ITEM foreign key (DC_GRUPO_ITEM, DC_ITEM)
      references TB_ITEM (DC_GRUPO_ITEM, DC_ITEM)
go

alter table TB_ORDEN_COMPRA_DETALLE
   add constraint FK_TB_ORDEN_FK_OCDET__TB_MONED foreign key (DC_MONEDA)
      references TB_MONEDA (DC_MONEDA)
go

alter table TB_ORDEN_COMPRA_DETALLE
   add constraint FK_TB_ORDEN_FK_OCDET__TB_UNIDA foreign key (DC_UNIDAD_MEDIDA)
      references TB_UNIDAD_MEDIDA (DC_UNIDAD_MEDIDA)
go

alter table TB_ORDEN_COMPRA_DETALLE
   add constraint FK_TB_ORDEN_FK_OC_DET_TB_ORDEN foreign key (DN_OC)
      references TB_ORDEN_COMPRA (DN_OC)
go

alter table TB_ORDEN_COMPRA_LOG_APROBADOR
   add constraint FK_TB_ORDEN_FK_LOG_AP_TB_MOTIV foreign key (DM_TIPO_MOTIVO, DC_TIPO_MOTIVO)
      references TB_MOTIVO (DM_TIPO_MOTIVO, DC_TIPO_MOTIVO)
go

alter table TB_ORDEN_COMPRA_LOG_APROBADOR
   add constraint FK_TB_ORDEN_FK_LOG_AP_TB_USUAR foreign key (DC_USUARIO)
      references TB_USUARIO (DC_USUARIO)
go

alter table TB_ORDEN_COMPRA_LOG_APROBADOR
   add constraint FK_TB_ORDEN_FK_OCLOG__TB_ORDEN foreign key (DN_OC)
      references TB_ORDEN_COMPRA (DN_OC)
go

alter table TB_ORDEN_COMPRA_LOG_ENVIO_MAIL
   add constraint FK_TB_ORDEN_FK_LOG_EN_TB_USUAR foreign key (DC_USUARIO)
      references TB_USUARIO (DC_USUARIO)
go

alter table TB_ORDEN_COMPRA_LOG_ESTADO
   add constraint FK_TB_ORDEN_FK_LOG_ES_TB_ESTAD foreign key (DM_ESTADO)
      references TB_ESTADOS_OC (DM_ESTADO)
go

alter table TB_ORDEN_COMPRA_LOG_ESTADO
   add constraint FK_TB_ORDEN_FK_LOG_ES_TB_ORDEN foreign key (DN_OC)
      references TB_ORDEN_COMPRA (DN_OC)
go

alter table TB_ROL_USUARIO
   add constraint FK_TB_ROL_U_FK_ROL_RU_TB_ROL foreign key (DN_ROL)
      references TB_ROL (DN_ROL)
go

alter table TB_ROL_USUARIO
   add constraint FK_TB_ROL_U_FK_ROL_US_TB_USUAR foreign key (DC_USUARIO)
      references TB_USUARIO (DC_USUARIO)
go

alter table TB_USUARIO
   add constraint FK_TB_USUAR_FK_LOG_ES_TB_ORDEN foreign key (DN_OC, DF_REGISTRO, DC_USUARIO, DM_ESTADO)
      references TB_ORDEN_COMPRA_LOG_ESTADO (DN_OC, DF_REGISTRO, DC_USUARIO, DM_ESTADO)
go

